"""
日程管理相关的异步 CRUD 操作
"""

from datetime import UTC, datetime, timedelta

from sqlalchemy import asc, func, or_, select
from sqlalchemy.ext.asyncio import AsyncSession

from src.models.family_model import Family, FamilyMember
from src.models.schedule_model import (
    Schedule,
    ScheduleParticipant,
    SchedulePriority,
    ScheduleRepeatType,
    ScheduleStatus,
)
from src.schemas.schedule_schema import ScheduleStats

# Schedule 模型的 CRUD 操作


async def get_schedule_by_id(
    db: AsyncSession, schedule_id: int, family_id: int
) -> Schedule | None:
    """根据 ID 获取日程"""
    result = await db.execute(
        select(Schedule).where(
            Schedule.id == schedule_id, Schedule.family_id == family_id
        )
    )
    schedule = result.scalar_one_or_none()
    return schedule


async def get_schedules(
    db: AsyncSession,
    family_id: int,
    skip: int = 0,
    limit: int = 100,
    start_date: datetime | None = None,
    end_date: datetime | None = None,
    status: ScheduleStatus | None = None,
    priority: SchedulePriority | None = None,
    search: str | None = None,
) -> tuple[list[Schedule], int]:
    """获取日程列表"""
    query = select(Schedule).where(Schedule.family_id == family_id)

    # 添加过滤条件
    if start_date:
        query = query.where(Schedule.start_time >= start_date)
    if end_date:
        query = query.where(Schedule.end_time <= end_date)
    if status:
        query = query.where(Schedule.status == status)
    if priority:
        query = query.where(Schedule.priority == priority)
    if search:
        query = query.where(
            or_(
                Schedule.title.contains(search),
                Schedule.description.contains(search),
            )
        )

    # 获取总数
    count_query = select(func.count()).select_from(query.subquery())
    total_result = await db.execute(count_query)
    total = total_result.scalar() or 0

    # 添加排序和分页
    query = query.order_by(asc(Schedule.start_time)).offset(skip).limit(limit)
    result = await db.execute(query)
    schedules = list(result.scalars().all())
    return schedules, total


async def get_schedules_by_date_range(
    db: AsyncSession,
    family_id: int,
    start_date: datetime,
    end_date: datetime,
) -> list[Schedule]:
    """根据日期范围获取日程"""
    query = select(Schedule).where(
        Schedule.family_id == family_id,
        Schedule.start_time >= start_date,
        Schedule.end_time <= end_date,
    )

    query = query.order_by(asc(Schedule.start_time))
    result = await db.execute(query)
    schedules = list(result.scalars().all())

    # 获取家庭名称
    if schedules:
        family_result = await db.execute(select(Family).where(Family.id == family_id))
        family = family_result.scalar_one_or_none()
        family_name = family.name if family else None

        # 添加名称到日程
        for schedule in schedules:
            schedule.family_name = family_name

    return schedules


async def create_schedule(
    db: AsyncSession,
    family_id: int,
    title: str,
    start_time: datetime,
    end_time: datetime,
    description: str | None = None,
    status: ScheduleStatus = ScheduleStatus.PENDING,
    priority: SchedulePriority = SchedulePriority.MEDIUM,
    repeat_type: ScheduleRepeatType = ScheduleRepeatType.NONE,
    repeat_until: datetime | None = None,
    is_reminder_enabled: bool = False,
    reminder_minutes: int | None = None,
    participant_ids: list[int] | None = None,
) -> Schedule:
    """创建日程"""
    # 获取家庭名称
    family_result = await db.execute(select(Family).where(Family.id == family_id))
    family = family_result.scalar_one_or_none()
    family_name = family.name if family else None

    # 创建日程
    db_schedule = Schedule(
        family_id=family_id,
        family_name=family_name,
        title=title,
        description=description,
        start_time=start_time,
        end_time=end_time,
        status=status,
        priority=priority,
        repeat_type=repeat_type,
        repeat_until=repeat_until,
        is_reminder_enabled=is_reminder_enabled,
        reminder_minutes=reminder_minutes,
    )

    db.add(db_schedule)
    await db.commit()
    await db.refresh(db_schedule)

    # 添加参与者
    if participant_ids:
        await add_schedule_participants(db, db_schedule.id, family_id, participant_ids)

    return db_schedule


async def update_schedule(
    db: AsyncSession, schedule_id: int, family_id: int, **kwargs
) -> Schedule | None:
    """更新日程"""
    schedule = await get_schedule_by_id(db, schedule_id, family_id)
    if not schedule:
        return None

    # 更新字段
    for key, value in kwargs.items():
        if hasattr(schedule, key):
            setattr(schedule, key, value)

    await db.commit()
    await db.refresh(schedule)
    return schedule


async def delete_schedule(db: AsyncSession, schedule_id: int, family_id: int) -> bool:
    """删除日程"""
    schedule = await get_schedule_by_id(db, schedule_id, family_id)
    if not schedule:
        return False

    # 删除相关参与者记录
    await db.execute(
        ScheduleParticipant.__table__.delete().where(
            ScheduleParticipant.schedule_id == schedule_id,
            ScheduleParticipant.family_id == family_id,
        )
    )

    # 删除日程
    await db.delete(schedule)
    await db.commit()
    return True


async def get_upcoming_schedules(
    db: AsyncSession,
    family_id: int,
    days_ahead: int = 7,
) -> list[Schedule]:
    """获取即将到来的日程"""
    now = datetime.now(UTC)
    end_date = now + timedelta(days=days_ahead)

    query = select(Schedule).where(
        Schedule.family_id == family_id,
        Schedule.start_time >= now,
        Schedule.start_time <= end_date,
        Schedule.status.in_([ScheduleStatus.PENDING, ScheduleStatus.IN_PROGRESS]),
    )

    query = query.order_by(asc(Schedule.start_time))
    result = await db.execute(query)
    schedules = list(result.scalars().all())

    # 获取家庭名称
    if schedules:
        family_result = await db.execute(select(Family).where(Family.id == family_id))
        family = family_result.scalar_one_or_none()
        family_name = family.name if family else None

        # 添加名称到日程
        for schedule in schedules:
            schedule.family_name = family_name

    return schedules


async def get_schedule_stats(
    db: AsyncSession,
    family_id: int,
    start_date: datetime | None = None,
    end_date: datetime | None = None,
) -> ScheduleStats | None:
    """获取日程统计信息"""
    query = select(Schedule).where(Schedule.family_id == family_id)

    if start_date:
        query = query.where(Schedule.start_time >= start_date)
    if end_date:
        query = query.where(Schedule.end_time <= end_date)

    result = await db.execute(query)
    schedules = list(result.scalars().all())

    if not schedules:
        return ScheduleStats(
            total_count=0,
            pending_count=0,
            in_progress_count=0,
            completed_count=0,
            cancelled_count=0,
            high_priority_count=0,
            urgent_count=0,
        )

    # 统计各种状态的数量
    total_count = len(schedules)
    pending_count = sum(1 for s in schedules if s.status == ScheduleStatus.PENDING)
    in_progress_count = sum(
        1 for s in schedules if s.status == ScheduleStatus.IN_PROGRESS
    )
    completed_count = sum(1 for s in schedules if s.status == ScheduleStatus.COMPLETED)
    cancelled_count = sum(1 for s in schedules if s.status == ScheduleStatus.CANCELLED)
    high_priority_count = sum(
        1 for s in schedules if s.priority == SchedulePriority.HIGH
    )
    urgent_count = sum(1 for s in schedules if s.priority == SchedulePriority.URGENT)

    return ScheduleStats(
        total_count=total_count,
        pending_count=pending_count,
        in_progress_count=in_progress_count,
        completed_count=completed_count,
        cancelled_count=cancelled_count,
        high_priority_count=high_priority_count,
        urgent_count=urgent_count,
    )


# ScheduleParticipant 模型的 CRUD 操作
async def get_schedule_participants(
    db: AsyncSession, schedule_id: int, family_id: int
) -> list[ScheduleParticipant]:
    """获取日程参与者列表"""
    result = await db.execute(
        select(ScheduleParticipant).where(
            ScheduleParticipant.schedule_id == schedule_id,
            ScheduleParticipant.family_id == family_id,
        )
    )
    participants = list(result.scalars().all())

    # 获取成员名称
    if participants:
        member_ids = list(set(p.member_id for p in participants))
        member_query = select(FamilyMember).where(
            FamilyMember.id.in_(member_ids), FamilyMember.family_id == family_id
        )
        member_result = await db.execute(member_query)
        members = {m.id: m.full_name for m in member_result.scalars().all()}

        # 获取家庭名称
        family_result = await db.execute(select(Family).where(Family.id == family_id))
        family = family_result.scalar_one_or_none()
        family_name = family.name if family else None

        # 添加名称到参与者
        for participant in participants:
            participant.family_name = family_name
            participant.member_name = members.get(participant.member_id)

    return participants


async def add_schedule_participants(
    db: AsyncSession, schedule_id: int, family_id: int, participant_ids: list[int]
) -> list[ScheduleParticipant]:
    """添加日程参与者"""
    # 验证成员是否存在且属于该家庭
    member_result = await db.execute(
        select(FamilyMember).where(
            FamilyMember.id.in_(participant_ids), FamilyMember.family_id == family_id
        )
    )
    members = member_result.scalars().all()
    valid_member_ids = [m.id for m in members]

    # 获取家庭名称
    family_result = await db.execute(select(Family).where(Family.id == family_id))
    family = family_result.scalar_one_or_none()
    family_name = family.name if family else None

    # 创建参与者记录
    participants = []
    for member_id in valid_member_ids:
        member = next(m for m in members if m.id == member_id)
        participant = ScheduleParticipant(
            family_id=family_id,
            family_name=family_name,
            schedule_id=schedule_id,
            member_id=member_id,
            member_name=member.full_name,
            is_required=True,
            has_confirmed=False,
        )
        db.add(participant)
        participants.append(participant)

    await db.commit()
    return participants


async def confirm_schedule_participation(
    db: AsyncSession, participant_id: int, family_id: int
) -> ScheduleParticipant | None:
    """确认日程参与"""
    result = await db.execute(
        select(ScheduleParticipant).where(
            ScheduleParticipant.id == participant_id,
            ScheduleParticipant.family_id == family_id,
        )
    )
    participant = result.scalar_one_or_none()

    if participant:
        participant.has_confirmed = True
        participant.confirmed_at = datetime.now(UTC)
        await db.commit()
        await db.refresh(participant)

    return participant
